﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;

namespace QQ2564874169.Core.ORM
{
    public interface IDbEntityQuery : IDbCore
    {
        IEnumerable<T> Gets<T>(SetWhere<T> where, int? timeout = null) where T : new();
        T Get<T>(SetWhere<T> where, int? timeout = null) where T : new();
        int Count<T>(SetWhere<T> where, int? timeout = null) where T : new();
        PageResult<T> Pager<T>(SetWhere<T> where, int? timeout = null) where T : new();
    }

    public abstract class DbEntityQuery: IDbEntityQuery
    {
        public static event Action<IDbEntityQuery> Created;

        public Hashtable Data { get; } = new Hashtable();
        public bool InTransaction => _query.InTransaction;
        public event EventHandler<TransactionFinishEventArgs> TransactionBefore
        {
            add => _query.TransactionBefore += value;
            remove => _query.TransactionBefore -= value;
        }
        public event EventHandler<TransactionFinishEventArgs> TransactionAfter
        {
            add => _query.TransactionAfter += value;
            remove => _query.TransactionAfter -= value;
        }
        public event EventHandler<SqlBeforeEventArgs> Before
        {
            add => _query.Before += value;
            remove => _query.Before -= value;
        }
        public event EventHandler<SqlAfterEventArgs> After
        {
            add => _query.After += value;
            remove => _query.After -= value;
        }
        public event EventHandler<SqlException> Error
        {
            add => _query.Error += value;
            remove => _query.Error -= value;
        }

        private IDbSqlQuery _query;

        protected DbEntityQuery(IDbSqlQuery query)
        {
            _query = query;

            Created?.Invoke(this);
        }

        public virtual void Dispose()
        {
            Data.Clear();
        }

        public ITransactionScope Transaction()
        {
            return _query.Transaction();
        }

        public IEnumerable<T> Gets<T>(SetWhere<T> where, int? timeout = null) where T : new()
        {
            if (where.ForUpdate && !InTransaction)
            {
                throw new Exception(nameof(where.ForUpdate) + "必须在事务中使用");
            }
            var param = ParseSetWhere(where);
            return _query.Query<T>(param.Command, param.Parameters, false, timeout);
        }

        public T Get<T>(SetWhere<T> where, int? timeout = null) where T : new()
        {
            var nw = where.Clone();
            nw.Take = 1;
            return Gets(nw, timeout).FirstOrDefault();
        }

        public int Count<T>(SetWhere<T> where, int? timeout = null) where T : new()
        {
            var nw = where.Clone();
            nw.count = null;
            nw.asc = null;
            nw.desc = null;
            nw.group = null;
            nw.Take = null;

            var param = ParseSetWhere(nw, e => e.Select = "COUNT(1)");
            return _query.QueryOne<int>(param.Command, param.Parameters, false, timeout);
        }

        public PageResult<T> Pager<T>(SetWhere<T> where, int? timeout = null) where T : new()
        {
            if (where.Take.HasValue == false || where.Take.Value <= 0)
            {
                throw new Exception("使用Pager方法必须设置Take并且大于0");
            }

            var num = where.Skip.GetValueOrDefault() / where.Take.Value;
            if (num * where.Take.Value != where.Skip.GetValueOrDefault())
            {
                throw new Exception("使用Pager方法时Skip和Take必须可以整除");
            }

            var nw = where.Clone();
            nw.ForUpdate = false;

            var data = ParseSetWhere(nw);
            nw.count = null;
            nw.asc = null;
            nw.desc = null;
            nw.group = null;
            nw.Take = null;
            nw.Skip = null;
            var count = ParseSetWhere(nw, e => e.Select = "COUNT(1)");
            var sql = count.Command + ";" + data.Command;
            using (var reader = _query.Multiple(sql, data.Parameters, false, timeout))
            {
                var rowcount = reader.ReadOne<int>();
                var rows = reader.Read<T>();

                var skip = where.Skip.GetValueOrDefault();
                var page = skip / where.Take.Value + 1;
                return new PageResult<T>(new PageSet(page, where.Take.Value), rowcount, rows);
            }
        }

        protected virtual ExecuteParam ParseSetWhere<T>(SetWhere<T> setWhere,Action<SqlStruct> mergeBefore = null) where T : new()
        {
            var execpm = new ExecuteParam();
            var table = FieldName(EntityParam.Parse(typeof(T)).SqlName);
            var select = new List<string>();
            var where = new List<string>();
            var order = "";
            var group = "";

            #region 
            if (setWhere.eq != null)
            {
                var ctx = EntityParam.Parse(typeof(T), setWhere.eq);
                if (ctx.Paramters.Count > 0)
                {
                    var ep = OnEq(ctx);
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere.neq != null)
            {
                var ctx = EntityParam.Parse(typeof(T), setWhere.neq);
                if (ctx.Paramters.Count > 0)
                {
                    var ep = OnNeq(ctx);
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere.less != null)
            {
                var ctx = EntityParam.Parse(typeof(T), setWhere.less);
                if (ctx.Paramters.Count > 0)
                {
                    var ep = OnLess(ctx);
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere.lessEq != null)
            {
                var ctx = EntityParam.Parse(typeof(T), setWhere.lessEq);
                if (ctx.Paramters.Count > 0)
                {
                    var ep = OnLessEq(ctx);
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere.greater != null)
            {
                var ctx = EntityParam.Parse(typeof(T), setWhere.greater);
                if (ctx.Paramters.Count > 0)
                {
                    var ep = OnGreater(ctx);
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere.greaterEq != null)
            {
                var ctx = EntityParam.Parse(typeof(T), setWhere.greaterEq);
                if (ctx.Paramters.Count > 0)
                {
                    var ep = OnGreaterEq(ctx);
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere.like != null)
            {
                var ctx = EntityParam.Parse(typeof(T), setWhere.like);
                if (ctx.Paramters.Count > 0)
                {
                    var ep = OnLike(ctx);
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere._in != null && setWhere._in.Count > 0)
            {
                foreach (var item in setWhere._in)
                {
                    var list = new List<EntityParam>();
                    var ctx = EntityParam.Parse(typeof(T), item);
                    if (ctx.Paramters.Count > 0)
                    {
                        list.Add(ctx);
                    }

                    var ep = OnIn(list.ToArray());
                    where.Add(ep.Command);
                    execpm.Parameters.Add(ep.Parameters);
                }
            }

            if (setWhere.asc != null)
            {
                var sql = OnAsc(setWhere.asc.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    order += sql;
                }
            }

            if (setWhere.desc != null)
            {
                var sql = OnDesc(setWhere.desc.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    if (order.Length > 0)
                    {
                        order += ",";
                    }
                    order += sql;
                }
            }

            if (setWhere.count != null)
            {
                var sql = OnCount(setWhere.count.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    select.Add(sql);
                }
            }

            if (setWhere.sum != null)
            {
                var sql = OnSum(setWhere.sum.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    select.Add(sql);
                }
            }

            if (setWhere.max != null)
            {
                var sql = OnMax(setWhere.max.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    select.Add(sql);
                }
            }

            if (setWhere.min != null)
            {
                var sql = OnMin(setWhere.min.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    select.Add(sql);
                }
            }

            if (setWhere.fields != null)
            {
                var sql = OnFields(setWhere.fields.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    select.Add(sql);
                }
            }

            if (setWhere.group != null)
            {
                var sql = OnGroup(setWhere.group.GetFields());
                if (string.IsNullOrEmpty(sql) == false)
                {
                    group = sql;
                }
            }
            #endregion

            var ss = new SqlStruct
            {
                Select = select.Count > 0 ? string.Join(",", select) : "*",
                Where = string.Join(" AND ", where),
                From = table,
                Group = group,
                Order = order
            };

            if (setWhere.ForUpdate)
            {
                OnForUpdate(ss);
            }

            if (setWhere.Skip.HasValue || setWhere.Take.HasValue)
            {
                OnTakeSkip(setWhere.Take.GetValueOrDefault(), setWhere.Skip.GetValueOrDefault(0), ss);
            }

            mergeBefore?.Invoke(ss);
            execpm.Command = MergeSql(ss);
            return execpm;
        }

        protected virtual string MergeSql(SqlStruct sqlStruct)
        {
            var sql = $"SELECT {sqlStruct.Select} FROM {sqlStruct.From}";

            if (string.IsNullOrEmpty(sqlStruct.Where) == false)
            {
                sql += " WHERE "+ sqlStruct.Where;
            }
            if (string.IsNullOrEmpty(sqlStruct.Order) == false)
            {
                sql += " ORDER BY " + sqlStruct.Order;
            }

            if (string.IsNullOrEmpty(sqlStruct.Group) == false)
            {
                sql += " GROUP BY " + sqlStruct.Group;
            }

            if (string.IsNullOrEmpty(sqlStruct.Backend) == false)
            {
                sql += sqlStruct.Backend;
            }

            return sql;
        }

        protected virtual string FieldName(string name)
        {
            return "[" + name + "]";
        }

        protected virtual string Precompile(string name)
        {
            return "@" + name;
        }

        protected virtual ExecuteParam OnEq(EntityParam param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            foreach (var k in param.Paramters.Keys)
            {
                var pn = "eq_" + k;
                list.Add(FieldName(k) + "=" + Precompile(pn));
                rs.Parameters.Add(pn, param.Paramters[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual ExecuteParam OnNeq(EntityParam param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            foreach (var k in param.Paramters.Keys)
            {
                var pn = "neq_" + k;
                list.Add(FieldName(k) + "!=" + Precompile(pn));
                rs.Parameters.Add(pn, param.Paramters[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual ExecuteParam OnLess(EntityParam param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            foreach (var k in param.Paramters.Keys)
            {
                var pn = "ls_" + k;
                list.Add(FieldName(k) + "<" + Precompile(pn));
                rs.Parameters.Add(pn, param.Paramters[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual ExecuteParam OnLessEq(EntityParam param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            foreach (var k in param.Paramters.Keys)
            {
                var pn = "lse_" + k;
                list.Add(FieldName(k) + "<=" + Precompile(pn));
                rs.Parameters.Add(pn, param.Paramters[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual ExecuteParam OnGreater(EntityParam param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            foreach (var k in param.Paramters.Keys)
            {
                var pn = "gt_" + k;
                list.Add(FieldName(k) + ">" + Precompile(pn));
                rs.Parameters.Add(pn, param.Paramters[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual ExecuteParam OnGreaterEq(EntityParam param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            foreach (var k in param.Paramters.Keys)
            {
                var pn = "gte_" + k;
                list.Add(FieldName(k) + ">=" + Precompile(pn));
                rs.Parameters.Add(pn, param.Paramters[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual ExecuteParam OnLike(EntityParam param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            foreach (var k in param.Paramters.Keys)
            {
                var pn = "lk_" + k;
                list.Add(FieldName(k) + " LIKE '%" + Precompile(pn) + "%'");
                rs.Parameters.Add(pn, param.Paramters[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual ExecuteParam OnIn(EntityParam[] param)
        {
            var rs = new ExecuteParam();
            var list = new List<string>();
            var map = new Dictionary<string, List<object>>();
            foreach (var item in param)
            {
                foreach (var k in item.Paramters.Keys)
                {
                    if (map.ContainsKey(k) == false)
                    {
                        map.Add(k, new List<object>());
                    }

                    map[k].Add(item.Paramters[k]);
                }
            }

            foreach (var k in map.Keys)
            {
                var pn = "in_" + k;
                list.Add(FieldName(k) + " IN " + Precompile(pn));
                rs.Parameters.Add(pn, map[k]);
            }

            rs.Command = string.Join(" AND ", list);
            return rs;
        }

        protected virtual string OnAsc(string[] fields)
        {
            return string.Join(",", fields.Select(i => i + " ASC"));
        }

        protected virtual string OnDesc(string[] fields)
        {
            return string.Join(",", fields.Select(i => i + " DESC"));
        }

        protected virtual string OnMax(string[] fields)
        {
            return string.Join(",", fields.Select(i => $"MAX({i}) {i}"));
        }

        protected virtual string OnMin(string[] fields)
        {
            return string.Join(",", fields.Select(i => $"MIN({i}) {i}"));
        }

        protected virtual string OnSum(string[] fields)
        {
            return string.Join(",", fields.Select(i => $"SUM({i}) {i}"));
        }

        protected virtual string OnCount(string[] fields)
        {
            return string.Join(",", fields.Select(i => $"COUNT({i}) {i}"));
        }

        protected virtual string OnGroup(string[] fields)
        {
            return string.Join(",", fields);
        }

        protected virtual void OnForUpdate(SqlStruct sqlStruct)
        {
            sqlStruct.From += " WITH (UPDLOCK)";
        }

        protected virtual void OnTakeSkip(int take, int skip, SqlStruct sqlStruct)
        {
            if (take == 0 && skip == 0)
            {
                return;
            }

            if (skip > 0)
            {
                var orderSql = sqlStruct.Order;
                if (string.IsNullOrEmpty(orderSql) == false)
                {
                    orderSql = " OVER(" + orderSql + ")";
                }

                sqlStruct.Select = $"ROW_NUMBER(){orderSql} PAGEROW," + sqlStruct.Select;
                sqlStruct.Order = "";
                var src = MergeSql(sqlStruct);
                sqlStruct.Select = "*";
                sqlStruct.From = $"({src}) T";
                sqlStruct.Group = "";
                sqlStruct.Where = $"PAGEROW>{skip}";
                if (take > 0)
                {
                    sqlStruct.Where += " AND PAGEROW<=" + (skip + take);
                }
            }
            else
            {
                sqlStruct.Select = $" TOP {take} {sqlStruct.Select}";
            }
        }

        protected virtual string OnFields(string[] fields)
        {
            return string.Join(",", fields);
        }

        public class SqlStruct
        {
            public string Select { get; set; }
            public string From { get; set; }
            public string Where { get; set; }
            public string Order { get; set; }
            public string Group { get; set; }
            public string Backend { get; set; }
        }
    }
}
